﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using SmartAgricultureService.Core;
using SmartAgricultureService.Models;
using SmartAgricultureService.Models.Tables;
using SmartAgricultureService.Tables;

namespace SmartAgricultureService.Jobs
{
    public sealed class JobRunner : IDisposable
    {
        private readonly DatabaseContext _database;
        public JobRunner()
        {
            var optionsBuilder = new DbContextOptionsBuilder<DatabaseContext>();
            var serverVersion = ServerVersion.AutoDetect(AppConfiguration.ConnectionString);
            optionsBuilder.UseMySql(AppConfiguration.ConnectionString, serverVersion);
            _database = new DatabaseContext(optionsBuilder.Options);
        }
        public async Task CreateReport()
        {
            IDbContextTransaction transaction = null;
            try
            {
                var reportDate = DateTime.Now.Date;
                transaction = await _database.Database.BeginTransactionAsync();
                string startDate = DateTime.Now.Date.ToString("yyyy-MM-dd"),
                    endDate = DateTime.Now.AddDays(1).Date.ToString("yyyy-MM-dd"),
                    sql =
                        $"WITH Hours AS(SELECT num AS Hour FROM Numbers WHERE num BETWEEN 0 AND 23), DeviceIds AS(SELECT DISTINCT DeviceId FROM Upload WHERE CreateDate >= '{startDate}' AND CreateDate < '{endDate}') SELECT h.Hour, d.DeviceId, a.AreaId, COALESCE(u.AvgTemperature, 0) AS AvgTemperature, COALESCE(u.MaxTemperature, 0) AS MaxTemperature, COALESCE(u.MinTemperature, 0) AS MinTemperature, COALESCE(u.AvgHumidity, 0) AS AvgHumidity, COALESCE(u.MaxHumidity, 0) AS MaxHumidity, COALESCE(u.MinHumidity, 0) AS MinHumidity, COALESCE(u.AvgIllumination, 0) AS AvgIllumination, COALESCE(u.MaxIllumination, 0) AS MaxIllumination, COALESCE(u.MinIllumination, 0) AS MinIllumination FROM Hours h CROSS JOIN DeviceIds d LEFT JOIN(SELECT HOUR(CreateDate) AS Hour, DeviceId, AVG(Temperature) AS AvgTemperature, MAX(Temperature) AS MaxTemperature, MIN(Temperature) AS MinTemperature, AVG(Humidity) AS AvgHumidity, MAX(Humidity) AS MaxHumidity, MIN(Humidity) AS MinHumidity, AVG(Illumination) AS AvgIllumination, MAX(Illumination) AS MaxIllumination, MIN(Illumination) AS MinIllumination FROM Upload WHERE CreateDate >= '{startDate}' AND CreateDate < '{endDate}' GROUP BY HOUR(CreateDate), DeviceId) u ON h.Hour = u.Hour AND d.DeviceId = u.DeviceId LEFT JOIN Device a ON d.DeviceId = a.Id ORDER BY h.Hour, d.DeviceId";
                var viewReports = await _database.Database.SqlQueryRaw<ViewReport>(sql).AsNoTracking().ToListAsync();
                List<ReportEntry> reportEntries = [];
                foreach (var viewReport in viewReports)
                {
                    var reportEntry = reportEntries.FirstOrDefault(x => x.AreaId == viewReport.AreaId);
                    var device = await _database.Devices.Include(x => x.Area).Include(x => x.Crop).AsNoTracking()
                        .FirstOrDefaultAsync(x => x.Id == viewReport.DeviceId);
                    if (reportEntry == null)
                    {
                        reportEntry = new ReportEntry
                        {
                            AreaId = device.AreaId,
                            ReportDate = reportDate,
                            AreaCode = device.Area.Code,
                            Id = Guid.NewGuid().ToString("N")
                        };
                        reportEntries.Add(reportEntry);
                    }
                    var detailEntry = new ReportDetailEntry
                    {
                        Hour = viewReport.Hour,
                        ReportId = reportEntry.Id,
                        DeviceId = viewReport.DeviceId,
                        MinHumidity = viewReport.MinHumidity,
                        MaxHumidity = viewReport.MaxHumidity,
                        AvgHumidity = viewReport.AvgHumidity,
                        Id = Guid.NewGuid().ToString("N"),
                        MinTemperature = viewReport.MinTemperature,
                        MaxTemperature = viewReport.MaxTemperature,
                        AvgTemperature = viewReport.AvgTemperature,
                        MinIllumination = viewReport.MinIllumination,
                        MaxIllumination = viewReport.MaxIllumination,
                        AvgIllumination = viewReport.AvgIllumination
                    };
                    var result = await Utils.GetAnswer(
                        $"帮忙分析一下，{device.Crop.Name}在这个温度最大值为{detailEntry.MaxTemperature}，最小值为{detailEntry.MinTemperature}，平均值为{detailEntry.AvgTemperature}；并且湿度最大值为{detailEntry.MaxHumidity}，最小值为{detailEntry.AvgHumidity}，平均值为{detailEntry.AvgHumidity}；并且光照最大值为{detailEntry.MaxIllumination}，最小值为{detailEntry.MinIllumination}，平均值为{detailEntry.AvgIllumination}时是否为最佳？");
                    detailEntry.Description = result;
                    reportEntry.ReportDetails.Add(detailEntry);
                }
                await _database.Reports.AddRangeAsync(reportEntries);
                await _database.SaveChangesAsync();
                await transaction.CommitAsync();
            }
            catch (Exception ex)
            {
                Logger.Instance.LogError("CreateJob", ex);
                if (transaction != null)
                    await transaction.RollbackAsync();
            }
        }
        public async Task ClearData()
        {
            IDbContextTransaction transaction = null;
            try
            {
                transaction = await _database.Database.BeginTransactionAsync();
                await _database.Logs.Where(x =>
                        EF.Functions.DateDiffDay(x.CreateDate, DateTime.Now.Date) > AppConfiguration.LogsResetDays)
                    .ExecuteDeleteAsync();
                await _database.Uploads.Where(x =>
                        EF.Functions.DateDiffDay(x.CreateDate, DateTime.Now.Date) > AppConfiguration.UploadsResetDays)
                    .ExecuteDeleteAsync();
                //report
                var reportQuery = _database.Reports.Where(x =>
                    EF.Functions.DateDiffDay(x.ReportDate, DateTime.Now.Date) > AppConfiguration.ReportClearDays);
                var reportIds = await reportQuery.Select(x => x.Id).ToListAsync();
                await _database.ReportDetails.Where(x => reportIds.Contains(x.Id)).ExecuteDeleteAsync();
                await reportQuery.ExecuteDeleteAsync();
                //commit
                await transaction.CommitAsync();
            }
            catch (Exception ex)
            {
                Logger.Instance.LogError("ClearData", ex);
                if (transaction != null)
                    await transaction.RollbackAsync();
            }
        }
        public void Dispose() => _database?.Dispose();
    }
}